package db;

import entities.*;

import java.sql.*;
import java.util.*;

public class DBBookManager {

	static Connection con = null;

	static PreparedStatement addMasterRecord, lookupMR, delMR, addCopy,
			searchName, searchGeneralName, findOwnerItems, findOwnerItemsNC, lookupCopy,
			returnAllItems, updateMasterItem, deleteCopy, updateCopyStmt, getItem;

	public static final int ALREADY_EXISTS = -2;

	private static void getConnection() throws SQLException {
		if (!Connector.isValidConnection(con)) {
			con = Connector.getConnection();
			// prepare statements
			addMasterRecord = con
					.prepareStatement("Insert into tblMasterItem (mastertype, title) values (?, ?)");
			lookupMR = con
					.prepareStatement("Select * from tblMasterItem where mastertype=? and title=?");
			lookupCopy = con.prepareStatement("Select * from tblCopies inner join tbllocation on tbllocation.locationId=tblCopies.locationId inner join tblmasteritem on tblmasteritem.itemid=tblCopies.itemID where copyId=?");
			delMR = con
					.prepareStatement("Delete from tblMasterItem where mastertype=? and title=?");
			searchName = con.prepareStatement(getString());
			findOwnerItems = con
					.prepareStatement("select * from   tblcopies left join tblmasteritem on "
							+ "tblcopies.itemID=tblmasteritem.itemID left join tbllocation on tbllocation.locationid=tblcopies.locationID "
							+ "where ownerID=? and copyStatus LIKE ? and masterType LIKE ? order by tblcopies.timeadded DESC LIMIT ?");
			findOwnerItemsNC = con
			.prepareStatement("select * from   tblcopies left join tblmasteritem on "
					+ "tblcopies.itemID=tblmasteritem.itemID left join tbllocation on tbllocation.locationid=tblcopies.locationID "
					+ "where ownerID=? order by tblcopies.timeadded DESC");

			searchName = con.prepareStatement(getSpecificString());
			searchGeneralName = con.prepareStatement(getString());
		    addCopy=con.prepareStatement("Insert into tblcopies (itemID,ownerID,locationID,copyStatus,visibility,timeAdded) values (?,?,?,?,?,now())");
		    getItem = con.prepareStatement("select * from tblmasteritem where itemID=?");
		    returnAllItems = con.prepareStatement("SELECT * FROM tblmasteritem");
		    updateCopyStmt = con.prepareStatement("update tblCopies set copyStatus = ?, visibility = ?, itemID = ?, locationID = ? where copyId = ?");
		    deleteCopy = con.prepareStatement("DELETE FROM tblCopies where copyId = ?");
		    updateMasterItem = con.prepareStatement("update tblmasteritem set masterType=?, title=? where itemID = ?");
		}
	}

	private static String getSpecificString() {

		return getString() + "and d.ownerID <> ? and (not exists (select initID from tblantifriends where targId = ? and initID=d.ownerID) and not exists(select targID from tblantifriends where initID=? and targID=d.ownerID))";
	}
	
	private static String getString() {
		String initSelect = "select e.title, l.locationName, d.copyStatus, CONCAT(u.firstName, \" \", u.lastName), d.ownerId, d.copyId  from tblmasteritem e";
		String ijCopies = " inner join tblcopies d on e.itemID = d.itemID ";
		String ijUser = "inner join tbluser u on d.ownerID = u.userID ";
		String ijLocation = "inner join tbllocation l on d.locationID = l.locationID ";
		String whereClause = "where e.mastertype LIKE ? and e.title LIKE ? and d.copystatus <> 'lost' and d.copystatus <> 'unavailable' and d.visibility <> 'private' ";
		return initSelect + ijCopies + ijUser + ijLocation + whereClause;
	}

	public static ArrayList<Item> findOwnerItems(int ownerID) {
		try {
			getConnection();
			findOwnerItemsNC.clearParameters();
			findOwnerItemsNC.setInt(1, ownerID);
			ResultSet rs = findOwnerItemsNC.executeQuery();
			ArrayList<Item> items = new ArrayList<Item>();
			while (rs.next()) {
				Item i = new Item();
				i.setCopyId(rs.getInt("copyID"));
				i.setMasterId(rs.getInt("itemID"));
				i.setUserId(ownerID);
				i.setLocationId(rs.getInt("locationID"));
				i.setLocation(rs.getString("locationName"));
				i.setVisible(rs.getString("visibility"));
				i.setCopyStatus(rs.getString("copyStatus"));
				i.setTitle(rs.getString("title"));
				i.setType(rs.getString("masterType"));
				i.setTimeAdded(rs.getDate("timeAdded"));
				i.setLastChanged(rs.getDate("lastChange"));
				items.add(i);
			}

			return items;
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<Item> findOwnerItems(int ownerID,
			String copyStatus, String type, int maxItems) {
		try {
			getConnection();
			findOwnerItems.clearParameters();
			findOwnerItems.setInt(1, ownerID);
			if (copyStatus == null)
				copyStatus = "%";
			if (type == null)
				type = "%";
			findOwnerItems.setString(2, copyStatus);
			findOwnerItems.setString(3, type);
			findOwnerItems.setInt(4, maxItems);
			ResultSet rs = findOwnerItems.executeQuery();
			ArrayList<Item> items = new ArrayList<Item>();
			while (rs.next()) {
				Item i = new Item();
				i.setCopyId(rs.getInt("copyID"));
				i.setMasterId(rs.getInt("itemID"));
				i.setUserId(ownerID);
				i.setLocationId(rs.getInt("locationID"));
				i.setLocation(rs.getString("locationName"));
				i.setVisible(rs.getString("visibility"));
				i.setCopyStatus(rs.getString("copyStatus"));
				i.setTitle(rs.getString("title"));
				i.setType(rs.getString("masterType"));
				i.setTimeAdded(rs.getDate("timeAdded"));
				i.setLastChanged(rs.getDate("lastChange"));
				items.add(i);
			}

			return items;
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return null;
		}
	}

	public static int findMasterRecord(String title, String type) {
		try {
			getConnection();

			lookupMR.clearParameters();
			lookupMR.setString(1, type);
			lookupMR.setString(2, title);
			ResultSet rs = lookupMR.executeQuery();
			while (rs.next()) {
				return rs.getInt("itemID");
			}
			return -1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}

	public static Item lookupCopy(int id) {
		Item i = new Item();

		try {
			getConnection();
			lookupCopy.clearParameters();
			lookupCopy.setInt(1, id);
			ResultSet rs = lookupCopy.executeQuery();
			while (rs.next()) {
				i.setCopyId(rs.getInt("copyID"));
				i.setMasterId(rs.getInt("itemID"));
				i.setUserId(rs.getInt("ownerID"));
				i.setLocationId(rs.getInt("locationID"));
				i.setLocation(rs.getString("locationName"));
				i.setVisible(rs.getString("visibility"));
				i.setCopyStatus(rs.getString("copyStatus"));
				i.setTitle(rs.getString("title"));
				i.setType(rs.getString("masterType"));
				i.setTimeAdded(rs.getDate("timeAdded"));
				return i;
			}
			return null;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
	}
	
	public static boolean deleteCopy(int copyId) {
		try {
			getConnection();
			deleteCopy.clearParameters();
			deleteCopy.setInt(1, copyId);
			int rs = deleteCopy.executeUpdate();
			if ( rs == 0 )
				return false;
			return true;
		}
		catch (SQLException e) {
			System.out.println(e);
			return false;
		}
	}
	
	public static boolean deleteMasterRecord(String title, String type) {
		try {
			getConnection();
			int id = findMasterRecord(title, type);
			if (id < 0)
				return false;// error
			delMR.clearParameters();
			delMR.setString(1, type);
			delMR.setString(2, title);
			int rs = delMR.executeUpdate();
			if (rs == 0)
				return false; // error
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			return false;
		}
	}

	public static int addMasterRecord(String title, String type) {
		try {
			getConnection();
			int id = findMasterRecord(title, type);
			if (id != -1)
				return ALREADY_EXISTS;
			addMasterRecord.clearParameters();
			addMasterRecord.setString(1, type);
			addMasterRecord.setString(2, title);
			int rs = addMasterRecord.executeUpdate();
			if (rs != 1)
				return -1; // error
			id = findMasterRecord(title, type);
			return id;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

			return -1;
		}

	}

	// "Insert into tblcopies
	// (itemID,ownerID,locationID,copyStatus,visibility,timeAdded)
	public static boolean addCopy(Item e) {
		try {
			getConnection();
			addCopy.clearParameters();
			addCopy.setInt(1, e.getMasterId());
			addCopy.setInt(2, e.getUserId());
			addCopy.setInt(3, e.getLocationId());
			addCopy.setString(4, e.getCopyStatus());
			addCopy.setString(5, e.getVisible());

			int result = addCopy.executeUpdate();
			return result == 1;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return false;
		}
	}

	public static ArrayList<Item> searchResults(String searchTerm,
			String searchType, int userId) {
		ArrayList<Item> results = new ArrayList<Item>();
		try {
			getConnection();
			searchName.clearParameters();
			searchName.setString(1, "%" + searchType + "%");
			searchName.setString(2, "%" + searchTerm + "%");
			searchName.setInt(3, userId);
			searchName.setInt(4, userId);
			searchName.setInt(5, userId);
			

			ResultSet rs = searchName.executeQuery();
			while (rs.next()) {
				Item item = new Item();
				item.setTitle(rs.getString(1));
				item.setLocation(rs.getString(2));
				item.setCopyStatus(rs.getString(3));
				item.setOwner(rs.getString(4));
				item.setCopyId(rs.getInt(6));
				item.setUserId(rs.getInt(5));
				
				if (item.getCopyStatus().equalsIgnoreCase("friends")) {
					if (DbRelationshipManager.friend(userId, rs.getInt(5))) {
						results.add(item);
					}
				} else {
					results.add(item);
				}

			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
		return results;
	}

	public static ArrayList<Item> searchResults(String searchTerm,
			String searchType) {
		ArrayList<Item> results = new ArrayList<Item>();
		try {
			getConnection();
			searchGeneralName.clearParameters();
			searchGeneralName.setString(1, "%" + searchType + "%");
			searchGeneralName.setString(2, "%" + searchTerm + "%");

			ResultSet rs = searchGeneralName.executeQuery();
			while (rs.next()) {
				Item item = new Item();
				item.setTitle(rs.getString(1));
				item.setLocation(rs.getString(2));
				item.setCopyStatus(rs.getString(3));
				item.setOwner(rs.getString(4));
				item.setCopyId(rs.getInt(6));
				item.setUserId(rs.getInt(5));
				results.add(item);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return null;
		}
		return results;
	}
	
	public static Item getItem(int masterId) {
		Item item = new Item();
		item.setMasterId(masterId);
		try {
			getConnection();
			getItem.clearParameters();
			getItem.setInt(1, masterId);
			ResultSet rs = getItem.executeQuery();
			while (rs.next()) {
				item.setType(rs.getString("masterType"));
				item.setTitle(rs.getString("title"));
				item.setTimeAdded(rs.getTime("timeAdded"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			return (Item)null;
		}
		return item;
	}
	
	public static ArrayList<Item> getAllItems() { //for admin use
		
		ResultSet rs;
		ArrayList<Item> results = new ArrayList<Item>();
		try {
			getConnection();
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM tblmasteritem order by title");
			while ((rs != null) && (rs.next())) {
				Item item = new Item();
				item.setMasterId(rs.getInt("itemID"));
				item.setType(rs.getString("masterType"));
				item.setTitle(rs.getString("title"));
				item.setTimeAdded(rs.getDate("timeAdded"));
				results.add(item);
			}
		}
		catch (SQLException e) {
			System.out.print(e.getMessage());
			return null;
		}
		return results;
	}

	public static boolean updateCopy(Item item) {
		
		try {
			getConnection();
			updateCopyStmt.clearParameters();
			updateCopyStmt.setString(1, item.getCopyStatus());
			updateCopyStmt.setString(2, item.getVisible());
			updateCopyStmt.setInt(3, item.getMasterId());
			updateCopyStmt.setInt(4, item.getLocationId());
			updateCopyStmt.setInt(5, item.getCopyId());
			int rs = updateCopyStmt.executeUpdate();
			if ( rs != 1 ) return false;
			return true;
		}
		catch (SQLException e) {
			System.out.println(e);
			return false;
		}
	}
	
	public static Boolean updateMasterRecord (Item item) {
		try {
			getConnection();
			updateMasterItem.clearParameters();
			updateMasterItem.setString(1, item.getType());
			updateMasterItem.setString(2, item.getTitle());
			updateMasterItem.setInt(3, item.getMasterId());
			if (updateMasterItem.executeUpdate() != 1) {
				return false;
			}
		}
		 catch (SQLException e) {
			System.out.print(e.getMessage());
			return false;
		 }
		 return true;
	}

}
